package nautilus.moneykeeper.dao;

import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;

import android.content.Context;
import android.content.res.AssetManager;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class MoneyDataHelper  extends SQLiteOpenHelper{

	private static final int DATABASE_VERSION = 1;
	private static String DB_NAME = "MONEYKEEPER_DB";
	
	public static String ACCOUNT_TABLE = "Account";
	public static final String[] columns_account = {"_id","_number", "_name", "_unit", 
		"_accountType", "_balance", "_datetime", "_desc", "_active"};
	
	public static String ACCOUNTTYPE_TABLE = "AccountType";
	public static final String[] columns_accounttype = {"_id", "_name", "_active"};
	
	public static String PAYMENTMETHOD_TABLE = "PaymentMethod";
	public static final String[] columns_payment_method = {"_id", "_name", "_active"};
	
	public static String CATEGORY_TABLE = "Category";
	public static final String[] columns_category = {"_id", "_name", "_direction", "_active"};
	
	public static String TRANSACTION_TABLE = "Trans";
	public static final String[] columns_trans = {"_id", "_type", "_cat", "_account", 
		"_method", "_amount", "_status", "_imagePath", "_datetime", "_desc", "_active"};
	
	public static String CURRENCYUNIT_TABLE = "CurrencyUnit";
	public static final String[] columns_currency_unit = {"_id", "_name", "_rate", "_nation", "_active"};
	
	public static String DEBT_TABLE = "Debt";
	public static final String[] columns_debt = {"_id", "_creditor", "_amount", "_datetime",
		"_expire", "_status", "_desc", "_active"};
	
	private Context _context;
	
	public MoneyDataHelper(Context context) {
		super(context, DB_NAME, null, DATABASE_VERSION);
		_context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		executeDB(db, "dbcreate.sql");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		android.util.Log.i("MoneyDataHelper", "DB Upgrade...");
		//It should be backed up before re-create new db
		//but at this time, I just create new one without backup
		onCreate(db);
	}
	
	/*
	 * read creating statements from asset file a execute them
	 * */
	private void executeDB(SQLiteDatabase db, String fileName){
		ByteArrayOutputStream outStream = new ByteArrayOutputStream();
		byte[] buf = new byte[1024];
		int len;
		AssetManager assetMgr = _context.getAssets();
		InputStream inputStream = null;
		String createStatement;
		try{
			inputStream = assetMgr.open(fileName);
			while( (len=inputStream.read(buf)) != -1 ){
				outStream.write(buf, 0, len);
			}
			inputStream.close();
			
			String[] creatingScript = outStream.toString().split(";");
			for(int i=0;i<creatingScript.length;i++){
				createStatement = creatingScript[i] + ";";
				db.execSQL(createStatement);
			}
			outStream.close();
			
			
			//Insert default records into database
			inputStream = assetMgr.open("initDb.sql");
			outStream.reset();
			while( (len=inputStream.read(buf)) != -1 ){
				outStream.write(buf, 0, len);
			}
			inputStream.close();
			
			creatingScript = outStream.toString().split(";");
			for(int i=0;i<creatingScript.length;i++){
				createStatement = creatingScript[i] + ";";
				db.execSQL(createStatement);
			}
			outStream.close();
			
		}catch(IOException e1){
			android.util.Log.e("MoneyDataHelper", e1.getMessage());
		}catch(SQLException e2){
			android.util.Log.e("MoneyDataHelper", e2.getMessage());
		}
	}
}

